package com.itheima.store.dao.impl;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.itheima.store.dao.UserDao;
import com.itheima.store.domain.User;
import com.itheima.store.utils.JDBCUtils;

/**
 * 用户模块的DAO的实现类
 * 
 * @author admin
 *
 */
public class UserDaoImpl implements UserDao {

	@Override
	public List<User> findByUsername(String username) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from user where user_name = ?";
		List<User> user = queryRunner.query(sql, new BeanListHandler<User>(User.class), username);
		return user;
	}

	@Override
	public void save(User user) throws SQLException {
//		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
//		String sql = "insert into user values (?,?,?,?,?,?,?,?,?,?,?)";
//		Object[] params = { user.getUser_id(), user.getUser_name(), user.getUser_password(), user.getUser_name(), user.getEmail(),
//				user.getTelephone(), user.getBirthday(), user.getSex(), user.getState(), user.getCode(),user.getLock() };
//		queryRunner.update(sql, params);
	}


	@Override
	public User findByCode(String code) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from user where code = ?";
		User user = queryRunner.query(sql, new BeanHandler<User>(User.class), code);
		return user;
	}

	@Override
	public void update(User user) throws SQLException {
//		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
//		String sql = "update user set username = ?,password = ?,name=?,email=?,telephone=?,birthday=?,sex=?,state=?,code=? ,lock=? where uid = ?";
//		Object[] params = {  user.getUser_name(), user.getUser_password(), user.getUser_name(), user.getEmail(),
//				user.getTelephone(), user.getBirthday(), user.getSex(), user.getState(), user.getCode(),user.getUser_id(),user.getLock() };
//		queryRunner.update(sql, params);
	}
	
	public void updateEdit(User user) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "update user set user_name = ?,user_password = ?,user_sex=?,user_phone=?,isadmin=?,isforbidden=?  where user_id = ?";
		Object[] params = {  user.getUser_name(), user.getUser_password(),user.getUser_sex(), user.getUser_phone(),user.getIsadmin(), user.getIsforbidden(),user.getUser_id() };
		queryRunner.update(sql, params);
	}

	@Override
	public User login(User user) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from user where user_name = ? and user_password = ? and isadmin=0 ";
		User existUser = queryRunner.query(sql, new BeanHandler<User>(User.class), user.getUser_name(),user.getUser_password());
		return existUser;
	}

	@Override
	public List<User> findAll() throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from user ";
		return queryRunner.query(sql, new BeanListHandler<User>(User.class));
	}

	@Override
	public Integer findAllByPage() throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select count(*) from user ";
		Long count = (Long) queryRunner.query(sql, new ScalarHandler());
		return count.intValue();
	}

	@Override
	public List<User> findAllByPage(int begin, int pageSize,String search) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = null;
		List<User> list;
		if(null ==search || "".equals(search)) {
			sql = "select * from user order by createTime limit ?,?";
			list = queryRunner.query(sql, new BeanListHandler<User>(User.class), begin,pageSize);
			return list;
		}
		else {
			
			 sql = "select * from user where 1=1 and user_name like binary ? order by createTime limit ?,?";
			 list = queryRunner.query(sql, new BeanListHandler<User>(User.class), "%"+search+"%",begin,pageSize);

		}
		return list;
		
	}

	@Override
	public User findUserByUid(String uid) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql ="select * from user where user_id =?";
		User user = queryRunner.query(sql, new BeanHandler<User>(User.class), uid);
		return user;
	}

	@Override
	public void lockUser(String uid, String lock) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql ="update user set user.isforbidden=? where user_id=?";
		queryRunner.update(sql, lock,uid);
	}

	@Override
	public void delUser(String uid) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql ="delete from user where user_id=?";
		queryRunner.update(sql, uid);
	}

	@Override
	public void add(User user) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql ="insert into user(user_id ,user_name,user_sex,user_dept_id,user_position_id,user_phone,isadmin,createTime,createPerson_id,createPerson_name)values(?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {user.getUser_id(),user.getUser_name(),user.getUser_sex(),user.getDept().getDept_id(),user.getPost().getPost_id(),user.getUser_phone(),user.getIsadmin(),user.getCreateTime(),user.getCreatePerson_id(),user.getCreatePerson_name()};
		queryRunner.update(sql, params);
	}

	@Override
	public Integer findAllByPage(String search) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select count(*) from user where user_name like binary ? ";
		Long count = (Long) queryRunner.query(sql, new ScalarHandler(),"%"+search+"%");
		return count.intValue();
	}

	@Override
	public User loginAdmin(User user) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from user where user_name = ? and user_password =? and isadmin=? ";
		User existuserAdmin = queryRunner.query(sql, new BeanHandler<User>(User.class), user.getUser_name(),user.getUser_password(),1);
		return existuserAdmin;
	}

	@Override
	public void alertPassword(User user) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "update user set user_password = ? where user_id =?" ;
		queryRunner.update(sql, user.getUser_new_password(),user.getUser_id());
	}

	@Override
	public User findByUsernameAndPassword(String user_name, String user_password) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql  = "select * from user where user_name=? and user_password =?";
		return queryRunner.query(sql, new BeanHandler<User>(User.class), user_name,user_password);
	}

	@Override
	public User loginDing(String name) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from user where user_name = ? ";
		User existUser = queryRunner.query(sql, new BeanHandler<User>(User.class),name);
		return existUser;
	}

	@Override
	public void addByMap(Map map) throws SQLException {
	
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql ="insert into user(user_id ,user_name,user_account,user_password,user_sex,user_dept_id,user_position_id,user_phone,isadmin,isforbidden,createTime,createPerson_id,createPerson_name)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		  //通过map.entrySet()方法
		  //方法一：循环map里面的每一对键值对，然后获取key和value
		int i=1;
		 Iterator it = map.entrySet().iterator();
		  while (it.hasNext()) {
			i++;
			Date date=new Date();                             
	        SimpleDateFormat temp=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
	        String date1=temp.format(date);  
	        Date date2 = null;
			try {
				date2 = temp.parse(date1);
			} catch (ParseException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} 
		   Map.Entry entry = (Map.Entry) it.next();
		   Object key = entry.getKey();
		   Object value = entry.getValue();
		   System.out.println("key=" + key + " value=" + value);
		   Object[] params = {key.toString(),value.toString(),i,"666666","1","3d68771e8b354068ad7f2f4ada8677c5","a8ca6f42882a4ad091ab32bd6a86d82c","18356986607","0","0",date2,"1","系统同步"};
		   queryRunner.update(sql, params);
		  }
		
		
		
		
	}
	
	

}
